package com.iyundao.io.mapper;


import com.iyundao.io.model.PartyMember;
import com.iyundao.io.model.PartyMemberOrganization;
import com.iyundao.io.tools.PartymemberVo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public interface PartyMemberCustomMapper {
    @Select("select p.id, p.partymembername, " +
            " p.partymembersex, p.partymemberbirthday, " +
            " p.partymemberjointime, p.partymemberphone, " +
            " p.partymemberaddress, i.integralsum, " +
            " i.integralnow,sr.*,sd.real_name name from partymember p " +
            " left join sys_user s " +
            " on p.partymemberphone = s.phone " +
            " left join user_role ur " +
            " on ur.uid = s.sid " +
            " left join sys_role sr " +
            " on sr.sid = ur.rid " +
            "LEFT JOIN user_dept ud " +
            "on ud.uid = s.sid " +
            "LEFT JOIN sys_dept sd " +
            "on sd.sid=ud.did " +
            " left join integral i " +
            " on i.partymemberid=p.id " +
            "where p.id in " +
            "(select partymemberid from partymemberorganization " +
            "where organizationid " +
            "in (select sid from sys_dept where sid=#{organizationid,jdbcType=VARCHAR} or parent_id= #{organizationid,jdbcType=VARCHAR}) " +
            "and ISNULL(outtime))")
    @Results({
            @Result(column="sid", property="sid", jdbcType= JdbcType.CHAR, id=true),
            @Result(column="real_name", property="realName", jdbcType=JdbcType.VARCHAR),
            @Result(column="show_name", property="showName", jdbcType=JdbcType.VARCHAR),
            @Result(column="parent_id", property="parentId", jdbcType=JdbcType.CHAR),
            @Result(column="detailed", property="detailed", jdbcType=JdbcType.VARCHAR),
            @Result(column="flag", property="flag", jdbcType=JdbcType.VARCHAR),
            @Result(column="create_date", property="createDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="create_id", property="createId", jdbcType=JdbcType.CHAR),
            @Result(column="orde_by", property="ordeBy", jdbcType=JdbcType.TINYINT)
    })
    List<PartymemberVo> selectByOrganizationId(String organizationid);

    @Select("select p.id, p.partymembername, " +
            " p.partymembersex, p.partymemberbirthday, " +
            " p.partymemberjointime, p.partymemberphone, " +
            " p.partymemberaddress, i.integralsum, " +
            " i.integralnow,sr.*,sd.real_name name from partymember p " +
            " left join sys_user s " +
            " on p.partymemberphone = s.phone " +
            " left join user_role ur " +
            " on ur.uid = s.sid " +
            " left join sys_role sr " +
            " on sr.sid = ur.rid " +
            "LEFT JOIN user_dept ud " +
            "on ud.uid = s.sid " +
            "LEFT JOIN sys_dept sd " +
            "on sd.sid=ud.did " +
            " left join integral i " +
            " on i.partymemberid=p.id " +
            "where p.id in " +
            "(select partymemberid from partymemberorganization " +
            "where organizationid " +
            "in (select sid from sys_dept where sid=#{organizationid,jdbcType=VARCHAR} or parent_id= #{organizationid,jdbcType=VARCHAR}) " +
            "and ISNULL(outtime)) and ur.rid = '4'")
    @Results({
            @Result(column="sid", property="sid", jdbcType= JdbcType.CHAR, id=true),
            @Result(column="real_name", property="realName", jdbcType=JdbcType.VARCHAR),
            @Result(column="show_name", property="showName", jdbcType=JdbcType.VARCHAR),
            @Result(column="parent_id", property="parentId", jdbcType=JdbcType.CHAR),
            @Result(column="detailed", property="detailed", jdbcType=JdbcType.VARCHAR),
            @Result(column="flag", property="flag", jdbcType=JdbcType.VARCHAR),
            @Result(column="create_date", property="createDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="create_id", property="createId", jdbcType=JdbcType.CHAR),
            @Result(column="orde_by", property="ordeBy", jdbcType=JdbcType.TINYINT)
    })
    List<PartymemberVo> selectByOrganizationIdNoManager(String organizationid);

    @Select("select p.id, p.partymembername, " +
            " p.partymembersex, p.partymemberbirthday, " +
            " p.partymemberjointime, p.partymemberphone, " +
            " p.partymemberaddress, i.integralsum, " +
            " i.integralnow,sr.*,sd.real_name name from partymember p " +
            " left join sys_user s " +
            " on p.partymemberphone = s.phone " +
            " left join user_role ur " +
            " on ur.uid = s.sid " +
            " left join sys_role sr " +
            " on sr.sid = ur.rid " +
            "LEFT JOIN user_dept ud " +
            "on ud.uid = s.sid " +
            "LEFT JOIN sys_dept sd " +
            "on sd.sid=ud.did " +
            " left join integral i " +
            " on i.partymemberid=p.id where p.id in " +
            "(select partymemberid from partymemberorganization " +
            "where ISNULL(outtime)) and ur.rid = '4'")
    @Results({
            @Result(column="sid", property="sid", jdbcType= JdbcType.CHAR, id=true),
            @Result(column="real_name", property="realName", jdbcType=JdbcType.VARCHAR),
            @Result(column="show_name", property="showName", jdbcType=JdbcType.VARCHAR),
            @Result(column="parent_id", property="parentId", jdbcType=JdbcType.CHAR),
            @Result(column="detailed", property="detailed", jdbcType=JdbcType.VARCHAR),
            @Result(column="flag", property="flag", jdbcType=JdbcType.VARCHAR),
            @Result(column="create_date", property="createDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="create_id", property="createId", jdbcType=JdbcType.CHAR),
            @Result(column="orde_by", property="ordeBy", jdbcType=JdbcType.TINYINT)
    })
    List<PartymemberVo> selectAllByOrganizationId();

    @Update("update partymemberorganization set outtime=#{outtime,jdbcType=DATE} where partymemberid=#{id,jdbcType=INTEGER} and ISNULL(outtime)")
    int rollOut(Date outtime, Integer id);

    @Select("select * from partymemberorganization " +
            "where organizationid = #{organizationid,jdbcType=VARCHAR} and partymemberid= #{partymemberid,jdbcType=INTEGER} and ISNULL(outtime)")
    PartyMemberOrganization selectRelation(String organizationid, Integer partymemberid);

    @Select("select count(*) from partymember where partymemberphone=#{partymemberphone,jdbcType=VARCHAR}")
    int selectByPhone(String partymemberphone);

    @Select("select * from partymember where partymemberphone=#{partymemberphone,jdbcType=VARCHAR}")
    PartyMember selectAllByPhone(String partymemberphone);

    @Select("select p.id,p.partymembername,p.partymembersex,p.partymemberaddress," +
            "p.partymemberbirthday,p.partymemberjointime,p.partymemberphone,p.partymembersex, " +
            "sd.real_name realname, sr.* from partymember p " +
            " left join sys_user s" +
            " on p.partymemberphone = s.phone" +
            " left join user_role ur" +
            " on ur.uid = s.sid" +
            " left join sys_role sr" +
            " on sr.sid = ur.rid " +
            "left JOIN partymemberorganization po " +
            "on po.partymemberid = p.id " +
            "left JOIN sys_dept sd " +
            "on sd.sid = po.organizationid " +
            "where p.partymembername like #{partymembername,jdbcType=VARCHAR} " +
            "and sd.sid in (select sid from sys_dept where sid = #{sid,jdbcType=VARCHAR} " +
            "or parent_id = #{sid,jdbcType=VARCHAR}) and ISNULL(po.outtime) and sr.rid = '4'")
    @Results({
            @Result(column="sid", property="sid", jdbcType=JdbcType.CHAR, id=true),
            @Result(column="real_name", property="realName", jdbcType=JdbcType.VARCHAR),
            @Result(column="show_name", property="showName", jdbcType=JdbcType.VARCHAR),
            @Result(column="parent_id", property="parentId", jdbcType=JdbcType.CHAR),
            @Result(column="detailed", property="detailed", jdbcType=JdbcType.VARCHAR),
            @Result(column="flag", property="flag", jdbcType=JdbcType.VARCHAR),
            @Result(column="create_date", property="createDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="create_id", property="createId", jdbcType=JdbcType.CHAR),
            @Result(column="orde_by", property="ordeBy", jdbcType=JdbcType.TINYINT)
    })
    List<PartymemberVo> selectByLikeName(String partymembername,String sid);

    @Select("select p.id,p.partymembername,p.partymembersex,p.partymemberaddress," +
            "p.partymemberbirthday,p.partymemberjointime,p.partymemberphone,p.partymembersex, " +
            "sd.real_name realname,sr.* from partymember p " +
            " left join sys_user s" +
            " on p.partymemberphone = s.phone" +
            " left join user_role ur" +
            " on ur.uid = s.sid" +
            " left join sys_role sr" +
            " on sr.sid = ur.rid " +
            "left JOIN partymemberorganization po " +
            "on po.partymemberid = p.id " +
            "left JOIN sys_dept sd " +
            "on sd.sid = po.organizationid " +
            "where p.partymemberbirthday >= #{date1,jdbcType=VARCHAR} " +
            "and p.partymemberbirthday <= #{date2,jdbcType=VARCHAR} " +
            "and sd.sid in (select sid from sys_dept where sid = #{sid,jdbcType=VARCHAR} or parent_id = #{sid,jdbcType=VARCHAR}) " +
            "and ISNULL(po.outtime) and sr.rid = '4'")
    @Results({
            @Result(column="sid", property="sid", jdbcType=JdbcType.CHAR, id=true),
            @Result(column="real_name", property="realName", jdbcType=JdbcType.VARCHAR),
            @Result(column="show_name", property="showName", jdbcType=JdbcType.VARCHAR),
            @Result(column="parent_id", property="parentId", jdbcType=JdbcType.CHAR),
            @Result(column="detailed", property="detailed", jdbcType=JdbcType.VARCHAR),
            @Result(column="flag", property="flag", jdbcType=JdbcType.VARCHAR),
            @Result(column="create_date", property="createDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="create_id", property="createId", jdbcType=JdbcType.CHAR),
            @Result(column="orde_by", property="ordeBy", jdbcType=JdbcType.TINYINT)
    })
    List<PartymemberVo> selectByAge(String date1,String date2,String sid);

    @Select("select p.id,p.partymembername,p.partymembersex,p.partymemberaddress," +
            "p.partymemberbirthday,p.partymemberjointime,p.partymemberphone,p.partymembersex, " +
            "sd.real_name realname,sr.* from partymember p " +
            " left join sys_user s" +
            " on p.partymemberphone = s.phone" +
            " left join user_role ur" +
            " on ur.uid = s.sid" +
            " left join sys_role sr" +
            " on sr.sid = ur.rid " +
            "left JOIN partymemberorganization po " +
            "on po.partymemberid = p.id " +
            "left JOIN sys_dept sd " +
            "on sd.sid = po.organizationid " +
            "where p.partymemberjointime >= #{date1,jdbcType=VARCHAR} " +
            "and p.partymemberjointime <= #{date2,jdbcType=VARCHAR} " +
            "and sd.sid in (select sid from sys_dept where sid = #{sid,jdbcType=VARCHAR} " +
            "or parent_id = #{sid,jdbcType=VARCHAR}) and ISNULL(po.outtime) and sr.rid = '4'")
    @Results({
            @Result(column="sid", property="sid", jdbcType=JdbcType.CHAR, id=true),
            @Result(column="real_name", property="realName", jdbcType=JdbcType.VARCHAR),
            @Result(column="show_name", property="showName", jdbcType=JdbcType.VARCHAR),
            @Result(column="parent_id", property="parentId", jdbcType=JdbcType.CHAR),
            @Result(column="detailed", property="detailed", jdbcType=JdbcType.VARCHAR),
            @Result(column="flag", property="flag", jdbcType=JdbcType.VARCHAR),
            @Result(column="create_date", property="createDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="create_id", property="createId", jdbcType=JdbcType.CHAR),
            @Result(column="orde_by", property="ordeBy", jdbcType=JdbcType.TINYINT)
    })
    List<PartymemberVo> selectByJoinTime(String date1,String date2,String sid);

    @Select("select p.*,sr.* from partymember p" +
            " left join sys_user s" +
            " on p.partymemberphone = s.phone" +
            " left join user_role ur" +
            " on ur.uid = s.sid" +
            " left join sys_role sr" +
            " on sr.sid = ur.rid " +
            " where id in " +
            "(select partymemberid from partymemberorganization where " +
            "organizationid = #{sid,jdbcType=VARCHAR} and ISNULL(outtime))")
    @Results({
            @Result(column="sid", property="sid", jdbcType=JdbcType.CHAR, id=true),
            @Result(column="real_name", property="realName", jdbcType=JdbcType.VARCHAR),
            @Result(column="show_name", property="showName", jdbcType=JdbcType.VARCHAR),
            @Result(column="parent_id", property="parentId", jdbcType=JdbcType.CHAR),
            @Result(column="detailed", property="detailed", jdbcType=JdbcType.VARCHAR),
            @Result(column="flag", property="flag", jdbcType=JdbcType.VARCHAR),
            @Result(column="create_date", property="createDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="create_id", property="createId", jdbcType=JdbcType.CHAR),
            @Result(column="orde_by", property="ordeBy", jdbcType=JdbcType.TINYINT)
    })
    List<PartymemberVo> selectBySid(String sid);

    @Select("select * from partymemberorganization where partymemberid=#{partymemberid,jdbcType=INTEGER} and ISNULL(outtime)")
    PartyMemberOrganization selectByPartymemberId(Integer partymemberid);

    @Select("SELECT * from partymember where " +
            "id not in " +
            "( " +
            "SELECT partymemberid from partymemberorganization where ISNULL(outtime) " +
            ") or id in (select partymemberid from partymemberorganization where organizationid = '0' " +
            "and ISNULL(outtime))")
    List<PartyMember> selectNoJoinDept();

    @Select("SELECT * from partymember where " +
            "id not in " +
            "( " +
            "SELECT partymemberid from partymemberorganization where ISNULL(outtime) " +
            ") or id in (select partymemberid from partymemberorganization where organizationid = '0' " +
            "and ISNULL(outtime)) " +
            "and partymembername like #{name,jdbcType=VARCHAR}")
    List<PartyMember> selectNoJoinDeptByName(String name);

}
